import pandas as pd
from sqlalchemy import create_engine

# 初始化数据库连接
# 按实际情况依次填写MySQL的用户名、密码、IP地址、端口、数据库名
# engine = create_engine("mysql+pymysql://root:@localhost:3306/python_test")

# 如果觉得上方的代码不够优雅，也可以安装下面的格式填写
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format("root", "root", "localhost", "3306", "python_test"))

sql_query = "select * from user;"
df_read = pd.read_sql_query(sql_query, engine)
print(df_read)


# DataFrame写入MySQL
# 新建DataFrame
df_write = pd.DataFrame({'id': [10, 27, 34, 46], 'username': ['张三', '李四', '王五', '赵六'], 'password': [80, 75, 56, 99]})
# 将user2储存为MySQL中的表，不储存index列
df_write.to_sql('user2', engine, index=False)
